package Model;

import Model.Connect;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.TreeSet;
import java.util.logging.Level;
import java.util.logging.Logger;

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author sio
 */
public class DAO {

    public static Collection<Dep> getLesDeps() {
        try {
            Connection con;
            java.sql.Statement req;
            java.sql.Statement req2;

            con = Connect.get();
            req = con.createStatement();
            ResultSet rs = req.executeQuery("SELECT DISTINCT departement FROM medecin");
            Collection<Dep> lesDeps = new TreeSet<Dep>();
            while (rs.next()) {

                req2 = con.createStatement();
                ResultSet rs2 = req2.executeQuery("SELECT * FROM medecin WHERE departement='"
                        + rs.getString("departement") + "'");
                Collection<Medecin> lesMeds = new TreeSet<Medecin>();
                while (rs2.next()) {
                    Medecin m = new Medecin(rs2.getString("nom"), rs2.getString("prenom"), rs2.getString("adresse"),
                            rs2.getString("tel"), rs2.getString("specialitecomplementaire"), rs2.getString("id"));
                    lesMeds.add(m);
                }
                rs2.close();
                req2.close();
                Dep d = new Dep(rs.getString("departement"), lesMeds);
                lesDeps.add(d);

            }
            rs.close();
            req.close();
            return lesDeps;
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;

    }

    public static Collection<Spe> getLesSpe() {
        try {
            Connection con;
            java.sql.Statement req;
            java.sql.Statement req2;

            con = Connect.get();
            req = con.createStatement();
            ResultSet rs = req.executeQuery("SELECT DISTINCT specialitecomplementaire FROM medecin");
            Collection<Spe> lesSpe = new TreeSet<Spe>();
            while (rs.next()) {
                if (rs.getString("specialitecomplementaire") != null) {
                    req2 = con.createStatement();
                    ResultSet rs2 = req2.executeQuery("SELECT * FROM medecin WHERE specialitecomplementaire='"
                            + rs.getString("specialitecomplementaire") + "'");
                    Collection<Medecin> lesMeds = new TreeSet<Medecin>();
                    while (rs2.next()) {
                        Medecin m = new Medecin(rs2.getString("nom"), rs2.getString("prenom"), rs2.getString("adresse"),
                                rs2.getString("tel"), rs2.getString("departement"), rs2.getString("id"));
                        lesMeds.add(m);
                    }
                    rs2.close();
                    req2.close();
                    Spe s = new Spe(rs.getString("specialitecomplementaire"), lesMeds);
                    lesSpe.add(s);
                }

            }
            rs.close();
            req.close();
            return lesSpe;
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;

    }
}
